﻿using Nlnet.Avalonia.Controls;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 畜牧业审核程序国产.Models
{
    internal class ShowMsg
    {
        #region 声明各畜种指标的数组
        private readonly string[] ArrStrIndicatorsOfCow = new string[] {"当季出栏数",
        "期末存栏头数01","肉牛存栏数02","能繁殖母牛存栏数03","奶牛存栏数04","在产奶牛存栏数05","当季增加头数06","自繁头数07","自繁肉牛头数08",
            "自繁奶牛头数09","购进头数10","购进肉牛头数11","购进奶牛头数12","期内减少头数13"
            ,"自宰肉牛数量14","出售肉牛数量15","出售肉牛金额（元）16","出售肉牛重量（公斤）17","当季生牛奶产量（公斤）18"
        };
        string[] ArrStrIndicatorsOfPig = new string[] {"当季出栏数", "期末存栏合计01", "25公斤以下仔猪存栏数02",
        "待育肥猪存栏数03","50公斤以上待育肥猪存栏数04","种猪存栏数05","能繁殖母猪存栏数06","当季增加头数07","自繁头数08","购进头数09",
        "当季减少头数10","自宰肥猪头数11", "出售肥猪头数（不包括出售待育肥猪）12","出售肥猪金额（元）13",
        "出售肥猪重量（公斤）14","其它原因减少（包括死亡、疫病捕杀）15","出售25公斤以下仔猪头数16","出售25公斤以下仔猪金额（元）17",
        "出售25公斤以下仔猪重量（公斤）18"};
        string[] ArrStrIndicatorsOfSheepAndGoat = new string[] {"当季出栏数", "期末存栏只数01", "山羊存栏只数02", "能繁殖母山羊存栏只数03", "绵(湖)羊存栏只数04", "能繁殖母绵羊存栏只数05", "期内增加只数06",
        "自繁只数07","山羊自繁只数08","绵(湖)羊自繁只数09","购进只数10","山羊购进只数11","绵(湖)羊购进只数12","期内减少只数13","自宰羊数量14","出售羊数量15","出售羊金额（元）16","出售羊重量（公斤）17","当季绵羊毛产量（公斤）18",
       "当季山羊绒产量（公斤）19",  };
        string[] ArrStrIndicatorsOfFeather = new string[] {"当季家禽出栏数","当季鸡出栏数","当季鸡存栏数", "期末存栏只数01", "肉用家禽存栏只数02", "肉鸡存栏只数03", "蛋用家禽存栏只数04", "蛋鸡存栏只数05", "期内减少只数06", "自宰家禽数量07",
        "自宰活鸡数量08","出售家禽数量09","出售活鸡数量10","出售家禽金额（元）11","出售活鸡金额（元）12","出售家禽重量（公斤）13","出售活鸡重量（公斤）14","当季禽蛋产量（公斤）15","鸡蛋产量（公斤）16"};
        #endregion
        #region tryToSetCartAndPieSeriesHere

        #endregion
        public DataTable GetAllOwnerMsg(KeyValuePair<string, string> CityItem, KeyValuePair<string, string> CountryItem, KeyValuePair<string, string> TownItem, string AnimalType, string WithYearSeasonTableName)
        {
            // MyDBOperator adoForAllOwnerMsg = new MyDBOperator();改用单例模式
            MyDBOperator adoForAllOwnerMsg = MyDBOperator.DBOperatorInstance;
            List<string> ListStrAllIndicators = new List<string>();
            string[] AnimalIndicators = null;
            if (AnimalType.Equals("猪"))
            {
                AnimalIndicators = ArrStrIndicatorsOfPig;
            }// some other animal type
            else if (AnimalType.Equals("牛"))
            {
                AnimalIndicators = ArrStrIndicatorsOfCow;
            }
            else if (AnimalType.Equals("羊"))
            {
                AnimalIndicators = ArrStrIndicatorsOfSheepAndGoat;
            }
            else
            {
                AnimalIndicators = ArrStrIndicatorsOfFeather;
            }
            for (int i = 0; i < AnimalIndicators.Length; i++)
            {
                ListStrAllIndicators.Add($"[{AnimalIndicators[i]}]");
            }
            string StrAllIndicatorsJoin = string.Join(",", ListStrAllIndicators);
            string sqlForGetAllIndicators = null;
            int 合计列数 = 0;
            if (CityItem.Value.Equals("全部"))
            {
                sqlForGetAllIndicators = $"select 市,县,乡,户主姓名,养殖户单位类型, {StrAllIndicatorsJoin} from {WithYearSeasonTableName} ";
                合计列数 = 5;
            }
            else if (CityItem.Value.Equals("全部") == false && CountryItem.Value.Equals("全部") && TownItem.Value.Equals("全部"))//全市的情况
            {
                sqlForGetAllIndicators = $"select 市,县,乡,户主姓名,养殖户单位类型, {StrAllIndicatorsJoin} from {WithYearSeasonTableName} where 市代码 = '{CityItem.Key}' order by 县代码";
                合计列数 = 5;
            }
            else if (CityItem.Value.Equals("全部") == false && CountryItem.Value.Equals("全部") == false && TownItem.Value.Equals("全部"))//全市的情况
            {
                sqlForGetAllIndicators = $"select 市, 县,乡,户主姓名,养殖户单位类型,{StrAllIndicatorsJoin} from {WithYearSeasonTableName} where 县代码 = '{CountryItem.Key}' order by 乡镇代码";
                合计列数 = 5;
            }
            else if (CityItem.Value.Equals("全部") == false && CountryItem.Value.Equals("全部") == false && TownItem.Value.Equals("全部") == false)//某个乡镇的情况
            {
                return null;
            }
            else
            {
                return null;
            }




            DataTable dt = adoForAllOwnerMsg.GetDataTable(sqlForGetAllIndicators);
            DataRow SumRow = dt.NewRow();
            //合计行,即最后一行的前几个位置写入"合计数"
            for (int i = 0; i < 合计列数; i++)
            {
                SumRow[i] = "合计";
            }

            for (int i = 合计列数; i < dt.Columns.Count; i++)
            {
                try
                {
                    SumRow[i] = (from row in dt.AsEnumerable()
                                 select row.Field<double>(i)).Sum();
                }
                catch { MessageBox.Show(dt.Columns[i].DataType.ToString()); }
            }
            dt.Rows.Add(SumRow);
            return dt;
        }
        public DataTable GetDtAllMsgs(KeyValuePair<string, string> CityItem, KeyValuePair<string, string> CountyItem, KeyValuePair<string, string> TownItem, string AnimalType, string WithYearSeasonTableName)
        {
            //MyDBOperator adoForShowMsg = new MyDBOperator();改用单例模式
            MyDBOperator adoForShowMsg = MyDBOperator.DBOperatorInstance;
            List<string> ListStrSumAllIndicators = new List<string>();
            string[] AnimalIndicators = null;
            if (AnimalType.Equals("猪"))
            {
                AnimalIndicators = ArrStrIndicatorsOfPig;
            }// some other animal type
            else if (AnimalType.Equals("牛"))
            {
                AnimalIndicators = ArrStrIndicatorsOfCow;
            }
            else if (AnimalType.Equals("羊"))
            {
                AnimalIndicators = ArrStrIndicatorsOfSheepAndGoat;
            }
            else
            {
                AnimalIndicators = ArrStrIndicatorsOfFeather;
            }
            for (int i = 0; i < AnimalIndicators.Length; i++)
            {
                ListStrSumAllIndicators.Add($"SUM([{AnimalIndicators[i]}]) as [规模户{AnimalIndicators[i]}]");
            }
            string StrAllIndicatorsSumJoin = string.Join(",", ListStrSumAllIndicators);
            //  string sqlForGetAllSumIndicators = $"SELECT 县,县代码,{StrAllIndicatorsJoin} from {TableName} where 市代码 = '{CityItem.Key}' group by 县,县代码";
            string sqlForGetAllSumIndicators = null;
            int 合计列数 = 0;
            if (CityItem.Value.Equals("全部"))//展现全部的情况
            {
                sqlForGetAllSumIndicators = $"select 市,市代码,{StrAllIndicatorsSumJoin} from {WithYearSeasonTableName} group by 市,市代码";
                合计列数 = 2;
            }
            else if (!CityItem.Value.Equals("全部") && CountyItem.Value.Equals("全部") && TownItem.Value.Equals("全部"))
            {
                //选择的市不为空,且选择的县为"全部",且选择的乡为"全部",说明展示的是市的数据
                //此时的sql语句为：
                sqlForGetAllSumIndicators = $"SELECT 县,县代码,{StrAllIndicatorsSumJoin} from {WithYearSeasonTableName} where 市代码 = '{CityItem.Key}' group by 县,县代码 order by 县代码";
                合计列数 = 2;
            }
            else if (!CityItem.Value.Equals("全部") && CountyItem.Value.Equals("全部") == false && TownItem.Value.Equals("全部"))
            {
                //选择的市不为空,且选择的县不为"全部",且选择的乡为"全部",说明展示的是市的县的数据
                //此时的sql语句为：
                sqlForGetAllSumIndicators = $"SELECT 乡,乡镇代码,{StrAllIndicatorsSumJoin} from {WithYearSeasonTableName} where 县代码 = '{CountyItem.Key}' group by 乡,乡镇代码 order by 乡镇代码";
                合计列数 = 2;
            }
            else if (CityItem.Value.Equals("全部") ==false && CountyItem.Value.Equals("全部") == false && TownItem.Value.Equals("全部") == false)
            {
                //选择的市不为空,且选择的县不为"全部",且选择的乡不为"全部",说明展示的是某个乡的数据
                //此时的sql语句为：
                List<string> ListStrForAllIndicatorsWithSquareBrackets = new List<string>();
                for (int i = 0; i < AnimalIndicators.Length; i++)
                {
                    //  ListStrForAllIndicatorsWithSquareBrackets[i] = $"[{AnimalIndicators[i]}]";要通过索引方式添加元素，需要先确保该索引位置已经存在元素。在你的代码中，你正在尝试通过索引方式给 ListStrForAllIndicatorsWithSquareBrackets 添加元素，但是你没有在循环之前初始化列表或者在循环中使用 Add 方法添加元素，所以会导致索引超出范围的错误
                    ListStrForAllIndicatorsWithSquareBrackets.Add($"[{AnimalIndicators[i]}]");
                }
                string StrAllIndicatorsJoin = string.Join(",", ListStrForAllIndicatorsWithSquareBrackets);
                sqlForGetAllSumIndicators = $"select 乡,乡镇代码,户主姓名,养殖户单位类型,{StrAllIndicatorsJoin} from {WithYearSeasonTableName} where 乡镇代码 = '{TownItem.Key}' ";
                合计列数 = 4;
            }
            DataTable dt = adoForShowMsg.GetDataTable(sqlForGetAllSumIndicators);
            DataRow SumRow = dt.NewRow();
            //合计行,即最后一行的前几个位置写入"合计数",datatable从sqlite取数后会变,因此这里先注释
            //for (int i = 0; i < 合计列数; i++)
            //{
            //    SumRow[i] = "合计";
            //}
            SumRow[0] = "规模户合计";
            for (int i = 合计列数; i < dt.Columns.Count; i++)//合计行后面每个指标的合计值
            {
                //var sum = (from row in dt.AsEnumerable()
                //           select row.Field<double>(i)).Sum();//选取row行的第i个字段的值转成double类型，并求和
                var Sum = dt.AsEnumerable().Sum(R => R.Field<double>(i));
                SumRow[i] = Sum;
            }
            dt.Rows.Add(SumRow);
            return dt;
        }
        public ObservableCollection<KeyValuePair<string, string>> GetAllCityItems(string WithYearSeasonTableName)
        {
            ObservableCollection<KeyValuePair<string, string>> CityCln = new ObservableCollection<KeyValuePair<string, string>>();
            //MyDBOperator adoForGetCity = new MyDBOperator();改用单例模式
            MyDBOperator adoForGetCity = MyDBOperator.DBOperatorInstance;
            string sqlForGetCity = $"select 市代码,市 from {WithYearSeasonTableName} group by 市代码,市";

            try
            {
                DataTable dt = adoForGetCity.GetDataTable(sqlForGetCity);
                if (dt.Rows.Count > 0)
                {
                    CityCln.Clear();
                    CityCln.Add(new KeyValuePair<string, string>("330", "全部"));
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dtRow = dt.Rows[i];
                        CityCln.Add(new KeyValuePair<string, string>(dtRow[0].ToString(), dtRow[1].ToString()));
                    }
                    return CityCln;
                }
                CityCln.Clear();
                CityCln.Add(new KeyValuePair<string, string>("0", $"未导入{WithYearSeasonTableName},或导入错误"));
                MessageBox.Show($"未导入{WithYearSeasonTableName},或导入错误");
                return CityCln;
            }
            catch (Exception)
            {
                CityCln.Clear();
                CityCln.Add(new KeyValuePair<string, string>("0", $"未导入{WithYearSeasonTableName},或导入错误"));
                MessageBox.Show($"未导入{WithYearSeasonTableName},或导入错误");
                return CityCln;
            }
            //DataTable dt = adoForGetCity.GetDataTable(sqlForGetCity);
            //if (dt == null)
            //    {
            //        CityCln.Clear();
            //        CityCln.Add(new KeyValuePair<string, string>("0", $"未导入{WithYearSeasonTableName},或导入错误"));

            //    }
            //    else
            //    {
            //        //CityCln.Clear();
            //        for (int i = 0; i < dt.Rows.Count; i++)
            //        {
            //            DataRow dtRow = dt.Rows[i];
            //            CityCln.Add(new KeyValuePair<string, string>(dtRow[0].ToString(), dtRow[1].ToString()));
            //        }
            //    }
            //    return CityCln;
            //}
        }

        public ObservableCollection<KeyValuePair<string, string>> GetAllIndicators(string AnimalType, string WithYearSeasonTableName)
        {
            //MyDBOperator adoForGetAllIndicators = new MyDBOperator();
            MyDBOperator adoForGetAllIndicators = MyDBOperator.DBOperatorInstance;
            string[] AnimalIndicators = null;
            if (AnimalType.Equals("猪"))
            {
                AnimalIndicators = ArrStrIndicatorsOfPig;
            }// some other animal type
            else if (AnimalType.Equals("牛"))
            {
                AnimalIndicators = ArrStrIndicatorsOfCow;
            }
            else if (AnimalType.Equals("羊"))
            {
                AnimalIndicators = ArrStrIndicatorsOfSheepAndGoat;
            }
            else
            {
                AnimalIndicators = ArrStrIndicatorsOfFeather;
            }
            List<string> ListStrForAllIndicatorsName = new List<string>();
            for (int i = 0; i < AnimalIndicators.Length; i++)
            {
                ListStrForAllIndicatorsName.Add($"[{AnimalIndicators[i]}]");
            }
            string StrAllIndicatorsNameJoin = string.Join(",", ListStrForAllIndicatorsName);
            string SqlForGetAllIndicatorsName = $"SELECT {StrAllIndicatorsNameJoin} FROM {WithYearSeasonTableName} WHERE 1=2";




            DataTable dt = adoForGetAllIndicators.GetDataTable(SqlForGetAllIndicatorsName);
            ObservableCollection<KeyValuePair<string, string>> IndicatorsCln = new ObservableCollection<KeyValuePair<string, string>>();
            try
            {
                //IndicatorsCln.Clear();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    IndicatorsCln.Add(new KeyValuePair<string, string>(i.ToString(), dt.Columns[i].ColumnName));
                }
                return IndicatorsCln;
            }
            catch (Exception)
            {
                // IndicatorsCln.Clear();
                IndicatorsCln.Add(new KeyValuePair<string, string>("0", $"未导入{WithYearSeasonTableName}报表"));
                return IndicatorsCln;
            }


            //DataTable dt = adoForGetAllIndicators.GetDataTable(SqlForGetAllIndicatorsName);
            //ObservableCollection<KeyValuePair<string, string>> IndicatorsCln = new ObservableCollection<KeyValuePair<string, string>>();
            //if (dt == null)
            //{
            //    //IndicatorsCln.Clear();
            //    IndicatorsCln.Add(new KeyValuePair<string, string>("0", $"未导入{WithYearSeasonTableName}报表"));
            //}
            //else
            //{
            //    for (int i = 0; i < dt.Columns.Count; i++)
            //    {
            //        IndicatorsCln.Add(new KeyValuePair<string, string>(i.ToString(), dt.Columns[i].ColumnName));
            //    }
            //}
            //return IndicatorsCln;
        }

        public DataTable GetSingleMsg(KeyValuePair<string, string> CityItem, KeyValuePair<string, string> CountryItem, KeyValuePair<string, string> TownItem,
         KeyValuePair<string, string> Indicator, string WithYearSeasonTableNameNow, string WithYearSeasonTableNamePre, string WithYearSeasonTableNameYOY)//调用者保证形参均不为空
        {
            //MyDBOperator adoForShowSingleMsg = new MyDBOperator();改用单例模式
            MyDBOperator adoForShowSingleMsg = MyDBOperator.DBOperatorInstance;
            if (adoForShowSingleMsg.CheckIfTableExists(WithYearSeasonTableNamePre) == false)
            {
                MessageBox.Show($"未导入{WithYearSeasonTableNamePre}报表");
                return null;
            }
            if (adoForShowSingleMsg.CheckIfTableExists(WithYearSeasonTableNameYOY) == false)
            {
                MessageBox.Show($"未导入{WithYearSeasonTableNameYOY}");
                return null;
            }
            if (CityItem.Value.Equals("全部"))//展现整个省的情况
            {
                //先复制三个表
                adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllCountryListOfThisCity", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });
                string sqlForFindAllCountryOfThisCountry = $"select 统计区划名称 as 市,乡镇区划代码 as 市代码 into TableAllCountryListOfThisCity from 全省区划代码 where len(乡镇区划代码) < 5";//市代码的长度小于5
                adoForShowSingleMsg.ExecuteBySql(sqlForFindAllCountryOfThisCountry);
                //建立第一次左连接
                //即建立本季度全市所有县的本季度数据的数据表
                string sqlForFirstLeftJoin = $"select t1.市 as 市,t1.市代码 as 市代码,t2.[本季度{Indicator.Value}] into No1LeftJoin from (TableAllCountryListOfThisCity as t1 left join " +
                    $"(select 市代码,sum([{Indicator.Value}])  as [本季度{Indicator.Value}] from {WithYearSeasonTableNameNow} group by 市代码) as t2 on t1.市代码 = t2.市代码)";
                adoForShowSingleMsg.ExecuteBySql(sqlForFirstLeftJoin);
                //第二次左连接
                //
                string sqlForSecondLeftJoin = $"select t1.市 as 市,t1.市代码 as 市代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}] into No2LeftJoin from (No1LeftJoin as t1 left join " +
                    $"(select 市代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {WithYearSeasonTableNamePre} group by 市代码) as t2 on t1.市代码 = t2.市代码)";
                adoForShowSingleMsg.ExecuteBySql(sqlForSecondLeftJoin);
                //第三次左连接 
                string sqlForThirdLeftJoin = $"select t1.市 as 市,t1.市代码 as 市代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                    $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                    $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                    $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, 100, 0), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅(%)] , " +
                    $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, 100, 0), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅(%)]  " +
                    $"into No3LeftJoin from ((select * from No2LeftJoin) as t1 left join (select 市代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {WithYearSeasonTableNameYOY} group by 市代码) as t2 on t1.市代码 = t2.市代码) order by t1.市代码 ";
                adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);
                //先把环比增幅和同比增幅的数据类型转成double
                string sqlForConvertToDouble = $"alter table No3LeftJoin alter column [环比增幅(%)] double";
                adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);
                sqlForConvertToDouble = $"alter table No3LeftJoin alter column [同比增幅(%)] double";
                adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);

                DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
                double SumOfIndicatorNow = (double)rowsum[0];
                double SumOfIndicatorPre = (double)rowsum[1];
                double SumOfIndicatorYOY = (double)rowsum[2];
                //计算占比
                List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
                if (SumOfIndicatorNow > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
                }
                if (SumOfIndicatorPre > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
                }
                if (SumOfIndicatorYOY > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
                }

                foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
                {
                    adoForShowSingleMsg.ExecuteBySql(item);
                }

                DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                DataRow SumRow = dt.NewRow();
                SumRow[0] = SumRow[1] = "合计";

                for (int i = 2; i < dt.Columns.Count; i++)
                {
                    SumRow[i] = (from row in dt.AsEnumerable()
                                 select row.Field<double?>(i)).Sum().Value;
                    //把饼图需要的数据(就一个value)返回去

                }
                SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                dt.Rows.Add(SumRow);


                return dt;
            }
            if (CountryItem.Value.Equals("全部"))//展现整个市的
            {

                //先复制三个表
                adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllCountryListOfThisCity", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });
                string sqlForFindAllCountryOfThisCountry = $"select 统计区划名称 as 县,乡镇区划代码 as 县代码 into TableAllCountryListOfThisCity from 全省区划代码 where 乡镇区划代码 like '{CityItem.Key}%' and len(乡镇区划代码) < 9";
                adoForShowSingleMsg.ExecuteBySql(sqlForFindAllCountryOfThisCountry);
                //建立第一次左连接
                //即建立本季度全市所有县的本季度数据的数据表
                string sqlForFirstLeftJoin = $"select t1.县 as 县,t1.县代码 as 县代码,t2.[本季度{Indicator.Value}] into No1LeftJoin from (TableAllCountryListOfThisCity as t1 left join " +
                    $"(select 县代码,sum([{Indicator.Value}])  as [本季度{Indicator.Value}] from {WithYearSeasonTableNameNow} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
                adoForShowSingleMsg.ExecuteBySql(sqlForFirstLeftJoin);
                //第二次左连接
                //
                string sqlForSecondLeftJoin = $"select t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}] into No2LeftJoin from (No1LeftJoin as t1 left join " +
                    $"(select 县代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {WithYearSeasonTableNamePre} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
                adoForShowSingleMsg.ExecuteBySql(sqlForSecondLeftJoin);
                //第三次左连接 
                string sqlForThirdLeftJoin = $"select t1.县 as 县,t1.县代码 as 县代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                    $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                    $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                    $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅(%)] , " +
                    $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅(%)]  " +
                    $"into No3LeftJoin from ((select * from No2LeftJoin) as t1 left join (select 县代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {WithYearSeasonTableNameYOY} group by 县代码) as t2 on t1.县代码 = t2.县代码) ";
                adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);
                //先把环比增幅和同比增幅的数据类型转成double
                string sqlForConvertToDouble = $"alter table No3LeftJoin alter column [环比增幅(%)] double";
                adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);
                sqlForConvertToDouble = $"alter table No3LeftJoin alter column [同比增幅(%)] double";
                adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);

                DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
                double SumOfIndicatorNow = (double)rowsum[0];
                double SumOfIndicatorPre = (double)rowsum[1];
                double SumOfIndicatorYOY = (double)rowsum[2];
                //计算占比
                List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
                if (SumOfIndicatorNow > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
                }
                if (SumOfIndicatorPre > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
                }
                if (SumOfIndicatorYOY > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
                }

                foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
                {
                    adoForShowSingleMsg.ExecuteBySql(item);
                }

                DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                DataRow SumRow = dt.NewRow();
                SumRow[0] = SumRow[1] = "合计";

                for (int i = 2; i < dt.Columns.Count; i++)
                {
                    SumRow[i] = (from row in dt.AsEnumerable()
                                 select row.Field<double?>(i)).Sum().Value;
                    //把饼图需要的数据(就一个value)返回去

                }
                SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                dt.Rows.Add(SumRow);


                return dt;
            }
            else if (CountryItem.Value.Equals("全部") == false && TownItem.Value.Equals("全部"))//显示某个县的全县情况
            {
                //要么就是先复制三个表,然后找出三个季度全部有的乡镇名和乡镇代码,作为第一个表,后面进行四次左连接
                adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllTownListOfThisCountry", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });
                string sqlForFindAllTownOfThisCountry = $"select 统计区划名称 as 乡,乡镇区划代码 as 乡镇代码 into TableAllTownListOfThisCountry from 全省区划代码 where 乡镇区划代码 like '{CountryItem.Key}%'and 乡镇区划代码 not like '%{CountryItem.Key}000%'";

                adoForShowSingleMsg.ExecuteBySql(sqlForFindAllTownOfThisCountry);
                //进行第一次左连接
                //建立本季度全县所有乡镇数据表
                string sqlForFirstLeftJoin = $"select t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, t2.[本季度{Indicator.Value}] into No1LeftJoin  from (TableAllTownListOfThisCountry as t1 left join" +
                    $" (select 乡,乡镇代码,sum([{Indicator.Value}]) as [本季度{Indicator.Value}] from {WithYearSeasonTableNameNow} group by 乡,乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码)   ";
                adoForShowSingleMsg.ExecuteBySql(sqlForFirstLeftJoin);
                //进行第二次左连接
                string sqlForSecondLeftJoin = $"select t1.乡 as 乡,t1.乡镇代码 as 乡镇代码,t1.[本季度{Indicator.Value}], t2.[上季度{Indicator.Value}] into No2LeftJoin  from (No1LeftJoin as t1 left join" +
                    $" (select 乡,乡镇代码,sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {WithYearSeasonTableNamePre} group by 乡,乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码)   ";
                adoForShowSingleMsg.ExecuteBySql(sqlForSecondLeftJoin);
                //进行第三次左连接
                string sqlForThirdLeftJoin = $"select t1.乡 as 乡,t1.乡镇代码 as 乡镇代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                    $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                    $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                    $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅(%)] , " +
                    $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅(%)]  " +
                    $"into No3LeftJoin from ((select * from No2LeftJoin) as t1 left join (select 乡,乡镇代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {WithYearSeasonTableNameYOY} group by 乡,乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码) ";
                //string sqlForThirdLeftJoin = $"select t1.乡镇名 as 乡镇名,t1.乡镇代码 as 乡镇代码,t1.[本季度{Indicator.Value}], t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}] as [同比季度{Indicator.Value}],t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量,t2.[同比季度{Indicator.Value}],t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量,iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅(%)] " +
                //      $", iif(t2.[同比季度{ Indicator.Value}] = 0, iif(t1.[本季度{ Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{ Indicator.Value}] -1) *100, 1)) as [同比增幅(%)] " +
                //    $"  from (No2LeftJoin as t1 left join" +
                //    $" (select 乡,乡镇代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {WithYearSeasonTableNamePre} group by 乡,乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码)    ";
                adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);
                //先把环比增幅和同比增幅的数据类型转成double
                string sqlForConvertToDouble = $"alter table No3LeftJoin alter column [环比增幅(%)] double";
                adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);
                sqlForConvertToDouble = $"alter table No3LeftJoin alter column [同比增幅(%)] double";
                adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);

                DataTable DtOfSum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin");
                //foreach (DataColumn column in DtOfSum.Columns)
                //{
                //    MessageBox.Show(column.DataType.ToString());
                //    column.DataType = typeof(double);
                //}

                DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
                double SumOfIndicatorNow = 0d;
                double SumOfIndicatorPre = 0d;
                double SumOfIndicatorYOY = 0d;
                if (rowsum.IsNull(0) == false)
                {
                    SumOfIndicatorNow = (double)rowsum[0];
                }
                if (rowsum.IsNull(1) == false)
                {
                    SumOfIndicatorPre = (double)rowsum[1];
                }
                if (rowsum.IsNull(2) == false)
                {
                    SumOfIndicatorYOY = (double)rowsum[2];
                }
                //计算占比
                List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
                if (SumOfIndicatorNow > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
                }
                if (SumOfIndicatorPre > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
                }
                if (SumOfIndicatorYOY > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
                }

                foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
                {
                    adoForShowSingleMsg.ExecuteBySql(item);
                }

                DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");

                //access不支持窗口函数over(),因此计算占比需要在linq中进行
                //double? SumOfSeasonNow = dt.AsEnumerable().Sum(row => row.Field<double?>(2));
                ////先判断本季度有没有数据,没有的话就不写入占比了,偷懒的写法

                //double? SumOfSeasonPre = (from row in dt.AsEnumerable()
                //                          select row.Field<double?>(3)).Sum();
                //double? SumOfSeasonYOY = dt.AsEnumerable().Sum(row => row.Field<double?>(4));


                //dt.Columns.Add("当季占比", typeof(double));
                //dt.Columns.Add("上季度占比", typeof(double));
                //dt.Columns.Add("同比季度占比", typeof(double));
                //foreach (DataRow row in dt.Rows)
                //{
                //    if (SumOfSeasonNow.HasValue)
                //    {
                //        if (row.IsNull(2) == false)
                //        {
                //            row["当季占比"] = Math.Round((double)row[2] / SumOfSeasonNow.Value * 100, 3);
                //        }
                //    }
                //    if (SumOfSeasonPre.HasValue)
                //    {
                //        if (!row.IsNull(3))
                //        {
                //            row["上季度占比"] = Math.Round((double)row[3] / SumOfSeasonPre.Value * 100, 3);
                //        }
                //    }
                //    if (SumOfSeasonYOY.HasValue)
                //    {
                //        if (!row.IsNull(4))
                //        {
                //            row["同比季度占比"] = Math.Round((double)row[4] / SumOfSeasonYOY.Value * 100, 3);
                //        }
                //    }
                //}




                //double? sum = dt.AsEnumerable().Sum(row => row.Field<double?>(2));
                //if (sum.HasValue == false)
                //{

                //}
                //else
                //{


                //    foreach (DataRow row in dt.Rows)
                //    {
                //        //if ((double)row[2] > 0)
                //        //{
                //        //    row["当季占比"] = Math.Round((double)row[2] / sum.Value, 2);//sum 是一个 double? 类型，而你尝试将它直接用作除数，需要将其转换为 double 类型才能进行除法运算。你可以使用 .Value 属性来获取可空类型的实际值。
                //        //}
                //        //先算环比增速
                //        //double? 上季度指标数 = row.Field<double?>(3);在sql语句里面已经写好了
                //        //double? 本季度指标数 = row.Field<double?>(2);
                //        var 本季度环比增幅 = row.Field<string>(7) ?? string.Empty;

                //        //这段代码是从 DataRow 中获取指定列的值，并将其转换为 string 类型。如果获取到的值为空（null），则使用空字符串（string.Empty）作为默认值。

                //        //具体解释如下：
                //        //row: 表示一个 DataRow 对象，通常用于表示数据表中的一行数据。
                //        //Field<string>(7): Field 方法用于从 DataRow 中获取指定列的值。在这里，< string > 表示要将该值转换为 string 类型，而(7) 表示要获取 DataRow 中索引为 7 的列的值。
                //        //?? string.Empty: 这是 null 合并运算符的使用，如果获取到的值为 null，则使用空字符串作为默认值。
                //        //综合起来，这行代码的作用是从指定 DataRow 中获取索引为 7 的列的值，并将其转换为 string 类型。如果获取到的值为空（null），则使用空字符串作为默认值。

                //        //先把两列数据类型转成double


                //        //if (上季度指标数.HasValue == false)//上季度指标数为空的话
                //        //{
                //        //    if (本季度指标数.HasValue == true && 本季度指标数.Value != 0)
                //        //    {
                //        //        row["环比增速(%)"] = 100;
                //        //    }

                //        //}
                //        //else
                //        //{

                //        //}
                //        double? value = row.Field<double?>(2);
                //        if (value.HasValue && value.Value > 0)
                //        {
                //            row["当季占比"] = Math.Round(value.Value / sum.Value, 2);//sum 是一个 double? 类型，而你尝试将它直接用作除数，需要将其转换为 double 类型才能进行除法运算。你可以使用 .Value 属性来获取可空类型的实际值。
                //        }
                //    }

                //}
                DataRow SumRow = dt.NewRow();
                SumRow[0] = SumRow[1] = "合计";

                for (int i = 2; i < dt.Columns.Count; i++)
                {
                    SumRow[i] = (from row in dt.AsEnumerable()
                                 select row.Field<double?>(i)).Sum().Value;
                    //把饼图需要的数据(就一个value)返回去

                }
                SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                dt.Rows.Add(SumRow);


                return dt;
            }
            else if (CountryItem.Value.Equals("全部") == false && TownItem.Value.Equals("全部") == false)//某个具体乡镇的
            {
                adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "AllOwnerNames", "No1LeftJoin", "No2LeftJoin", "No3LeftJoin" });
                //先获取这个乡镇所有的养殖户名单
                //string sqlForCombineAllOwnerNames = $"select * into  AllOwnerNames from (select 县,乡,乡镇代码,户主姓名,养殖户单位类型 from {WithYearSeasonTableNameNow}  where 乡镇代码 = '{TownItem.Key}') union (select 县,乡,乡镇代码,户主姓名,养殖户单位类型 from {WithYearSeasonTableNamePre}  where 乡镇代码 = '{TownItem.Key}') union (select 县,乡,乡镇代码,户主姓名,养殖户单位类型 from {WithYearSeasonTableNameYOY} where 乡镇代码  = '{TownItem.Key}')";
                //adoForShowSingleMsg.ExecuteBySql(sqlForCombineAllOwnerNames);
                //string sqlForFirstLeftJoin =  $"select t1.乡 as 乡,t1.乡镇代码 as 乡镇代码,t1.户主姓名,t1.养殖户类型, t2.[本季度{Indicator.Value}] into No1LeftJoin  from (AllOwnerNames as t1 left join" +
                //    $" (select 乡,乡镇代码,户主姓名,养殖户类型,户编码[{Indicator.Value}] as [本季度{Indicator.Value}] from {WithYearSeasonTableNameNow} ) as t2 on t1.户编码 = t2.户编码)   ";
                //adoForShowSingleMsg.ExecuteBySql(sqlForFirstLeftJoin);
                //string sqlForFistLeftJoin = $"( " +
                //$"(select 县,乡,乡镇代码,户主姓名,养殖户单位类型 from {WithYearSeasonTableNameNow}  where 乡镇代码 = '{TownItem.Key}') " +
                //$" union (select 县,乡,乡镇代码,户主姓名,养殖户单位类型 from {WithYearSeasonTableNamePre}  where 乡镇代码 = '{TownItem.Key}')" +
                //$")" +
                //$"union " +
                //$"(select 县,乡,乡镇代码,户主姓名,养殖户单位类型 from {WithYearSeasonTableNameYOY} where 乡镇代码  = '{TownItem.Key}')";
                //string sqlForFirstLeftJoin = " select *  from (select 县,乡,乡镇代码,户主姓名,养殖户单位类型 from 2024年第一季度A211  where 乡镇代码 = '330781104') union (select 县,乡,乡镇代码,户主姓名,养殖户单位类型 from 2023年第四季度A211  where 乡镇代码 = '330781104')";
                string sql = $"((select 县,乡,乡镇代码,户主姓名,养殖户单位类型 from {WithYearSeasonTableNameNow}  where 乡镇代码 = '{TownItem.Key}')" +
                    $" union (select 县,乡,乡镇代码,户主姓名,养殖户单位类型 from {WithYearSeasonTableNamePre}  where 乡镇代码 = '{TownItem.Key}')) " +
                    $"union (select 县,乡,乡镇代码,户主姓名,养殖户单位类型 from {WithYearSeasonTableNameYOY} where 乡镇代码  = '{TownItem.Key}')";
                DataTable dtOfAllOwnerNames = adoForShowSingleMsg.GetDataTable(sql);
                adoForShowSingleMsg.SetDatatableToANewTable(dtOfAllOwnerNames, "AllOwnerNames");
                string sqlForFirstJoin = $"select t1.县 as 县,t1.乡 as 乡,t1.乡镇代码 as 乡镇代码,t1.户主姓名 as 户主姓名,t1.养殖户单位类型 as 养殖户单位类型," +
                    $" t2.[本季度{Indicator.Value}] into No1LeftJoin  from (AllOwnerNames as t1 left join" +
                    $" (select 县,乡,乡镇代码,户主姓名,养殖户单位类型,[{Indicator.Value}] as [本季度{Indicator.Value}] from {WithYearSeasonTableNameNow} where 乡镇代码='{TownItem.Key}') as t2 on t1.户主姓名 = t2.户主姓名)   ";
                adoForShowSingleMsg.ExecuteBySql(sqlForFirstJoin);
                //进行第二次左连接
                string sqlForSecondLeftJoin = $"select t1.县,t1.乡,t1.乡镇代码,t1.户主姓名,t1.养殖户单位类型,t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}] into No2LeftJoin from " +
                $"(No1LeftJoin as t1 left join (select 户主姓名, [{Indicator.Value}] as [上季度{Indicator.Value}]  from {WithYearSeasonTableNamePre}) as t2 on t1.户主姓名 = t2.户主姓名)";

                adoForShowSingleMsg.ExecuteBySql(sqlForSecondLeftJoin);
                //进行第三次左连接
                //string sqlForThirdLeftJoin = $"select t1.县 as 县, t1.乡 as 乡,t1.乡镇代码 as 乡镇代码,t1.户主姓名 as 户主姓名,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                //    $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                //    $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                //    $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅(%)] , " +
                //    $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅(%)]  " +
                //    $"into No3LeftJoin from ((select * from No2LeftJoin) as t1 left join (select 户主姓名,[{Indicator.Value}] as [同比季度{Indicator.Value}] from {WithYearSeasonTableNameYOY} ) as t2 on t1.户主姓名 = t2.户主姓名) ";

                string sqlForThirdLeftJoin = $"select t1.户主姓名 as 户主姓名,t1.养殖户单位类型,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                   $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                   $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                   $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅(%)] , " +
                   $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅(%)]  " +
                   $"into No3LeftJoin from ((select * from No2LeftJoin) as t1 left join (select 户主姓名,[{Indicator.Value}] as [同比季度{Indicator.Value}] from {WithYearSeasonTableNameYOY} ) as t2 on t1.户主姓名 = t2.户主姓名) ";

                adoForShowSingleMsg.ExecuteBySql(sqlForThirdLeftJoin);
                //先把环比增幅和同比增幅的数据类型转成double
                string sqlForConvertToDouble = $"alter table No3LeftJoin alter column [环比增幅(%)] double";
                adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);
                sqlForConvertToDouble = $"alter table No3LeftJoin alter column [同比增幅(%)] double";
                adoForShowSingleMsg.ExecuteBySql(sqlForConvertToDouble);

                DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
                double SumOfIndicatorNow = 0d;
                double SumOfIndicatorPre = 0d;
                double SumOfIndicatorYOY = 0d;
                if (rowsum.IsNull(0) == false)
                {
                    SumOfIndicatorNow = (double)rowsum[0];
                }
                if (rowsum.IsNull(1) == false)
                {
                    SumOfIndicatorPre = (double)rowsum[1];
                }
                if (rowsum.IsNull(2) == false)
                {
                    SumOfIndicatorYOY = (double)rowsum[2];
                }

                //计算占比
                List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
                if (SumOfIndicatorNow > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
                }
                if (SumOfIndicatorPre > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
                }
                if (SumOfIndicatorYOY > 0)
                {
                    ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
                }

                foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
                {
                    adoForShowSingleMsg.ExecuteBySql(item);
                }

                DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                DataRow SumRow = dt.NewRow();
                SumRow[0] = SumRow[1] = "合计";

                for (int i = 2; i < dt.Columns.Count; i++)
                {
                    SumRow[i] = (from row in dt.AsEnumerable()
                                 select row.Field<double?>(i)).Sum().Value;
                    //把饼图需要的数据(就一个value)返回去

                }
                SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                dt.Rows.Add(SumRow);
                return dt;
            }
            return null;
        }
    }
}
